Imports System.Data.OleDb
Imports DTO

Public Class DiemTrungBinhDAO
    Inherits DataProviderDAO

    'Oanh
    'Ham tinh diem trung binh cua mot hoc sinh
    'Tham so dau vao : maLH, maNH, hocKy
    Public Function TinhDiemTrungBinh(ByVal maLH As Integer, ByVal maNH As Integer, ByVal hocKy As Integer) As DataTable
        Dim dt As New DataTable
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = Me.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String

        strSQL = "select  hs.HoTen, lh.TenLH , ROUND((dtb1.DiemTB + dtb2.DiemTB+dtb3.DiemTB+dtb4.DiemTB+dtb5.DiemTB + dtb6.DiemTB +dtb7.DiemTB+dtb8.DiemTB+dtb9.DiemTB) / 9,2) as DiemTrungBinhHocKy " + _
                    " from DiemTrungBinh dtb1,DiemTrungBinh dtb2,DiemTrungBinh dtb3,DiemTrungBinh dtb4,DiemTrungBinh dtb5,DiemTrungBinh dtb6,DiemTrungBinh dtb7,DiemTrungBinh dtb8,DiemTrungBinh dtb9, HocSinh hs, LopHoc lh, NamHoc nh" + _
                    " where hs.MaHS = dtb1.MaHS " + _
                    " and hs.MaHS = dtb2.MaHS " + _
                    " and hs.MaHS = dtb3.MaHS " + _
                    " and hs.MaHS = dtb4.MaHS " + _
                    " and hs.MaHS = dtb5.MaHS " + _
                    " and hs.MaHS = dtb6.MaHS " + _
                    " and hs.MaHS = dtb7.MaHS " + _
                    " and hs.MaHS = dtb8.MaHS " + _
                    " and hs.MaHS = dtb9.MaHS " + _
                    " and dtb1.MaMH =1 " + _
                    " and dtb2.MaMH =2 " + _
                    " and dtb3.MaMH =3 " + _
                    " and dtb4.MaMH =4 " + _
                    " and dtb5.MaMH =5 " + _
                    " and dtb6.MaMH =6 " + _
                    " and dtb7.MaMH =7 " + _
                    " and dtb8.MaMH =8 " + _
                    " and dtb9.MaMH =9  " + _
                    " and lh.MaLH = " + maLH.ToString() + _
                    " and lh.MaLH=hs.MaLH " + _
                    " and dtb1.HocKy = " + hocKy.ToString() + _
                    " and dtb2.HocKy = " + hocKy.ToString() + _
                    " and dtb3.HocKy = " + hocKy.ToString() + _
                    " and dtb4.HocKy = " + hocKy.ToString() + _
                    " and dtb5.HocKy = " + hocKy.ToString() + _
                    " and dtb6.HocKy = " + hocKy.ToString() + _
                    " and dtb7.HocKy = " + hocKy.ToString() + _
                    " and dtb8.HocKy = " + hocKy.ToString() + _
                    " and dtb9.HocKy = " + hocKy.ToString() + _
                    " and nh.MaNH = lh.MaNam " + _
                    " and nh.MaNH =" + maNH.ToString() + _
                    " ORDER BY hs.HoTen"



        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter(strSQL, cn)
        da.Fill(dt)
        'B5: Dong ket noi CSDL
        cn.Close()
        Return dt
    End Function

    'BCTKM : so luong dat , theo khoi
    Public Function SoLuongDat(ByVal maKhoi As Integer, ByVal maNam As Integer, ByVal maMon As Integer, ByVal hocKy As Integer) As DataTable
        Dim dt As New DataTable

        Dim cn As OleDbConnection
        cn = Me.ConnectionData()

        Dim strSQL As String
        strSQL = "SELECT LH.TenLH, Count(DTB.DiemTB) as SoLuongDat " + _
                 "FROM Khoi K, LopHoc LH, HocSinh HS, DiemTrungBinh DTB " + _
                 "WHERE LH.MaKhoi = K.MaK and K.MaK = " + maKhoi.ToString() + _
                 " and LH.MaLH = HS.MaLH and HS.MaHS = DTB.MaHS  and LH.MaNam = " + maNam.ToString() + _
                 " and DTB.MaMH = " + maMon.ToString() + " and DTB.HocKy = " + hocKy.ToString() + " and  DTB.DiemTB >=5 " + _
                 "GROUP BY HS.MaLH, LH.TenLH"

        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter(strSQL, cn)
        da.Fill(dt)
        cn.Close()
        Return dt
    End Function

    'BCTKM : so luong dat , theo tat ca cac khoi
    Public Function SoLuongDat_2(ByVal maNam As Integer, ByVal maMon As Integer, ByVal hocKy As Integer) As DataTable
        Dim dt As New DataTable

        Dim cn As OleDbConnection
        cn = Me.ConnectionData()

        Dim strSQL As String
        strSQL = "SELECT LH.TenLH, Count(DTB.DiemTB) as SoLuongDat " + _
                 "FROM Khoi K, LopHoc LH, HocSinh HS, DiemTrungBinh DTB " + _
                 "WHERE LH.MaLH = HS.MaLH and HS.MaHS = DTB.MaHS  and LH.MaKhoi = K.MaK and LH.MaNam = " + maNam.ToString() + _
                 " and DTB.MaMH = " + maMon.ToString() + " and DTB.HocKy = " + hocKy.ToString() + " and  DTB.DiemTB >=5 " + _
                 "GROUP BY HS.MaLH, LH.TenLH"

        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter(strSQL, cn)
        da.Fill(dt)
        cn.Close()
        Return dt
    End Function

    'BCTKHK : tinh diem trung binh cuoi ky, theo hoc ky, namhoc, tat ca cac lop
    Public Function DiemTB_TatCa(ByVal maNH As Integer, ByVal hocKy As Integer) As DataTable
        Dim dt As New DataTable
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = Me.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String

        strSQL = "select  hs.HoTen, lh.TenLH , ROUND((dtb1.DiemTB + dtb2.DiemTB+dtb3.DiemTB+dtb4.DiemTB+dtb5.DiemTB + dtb6.DiemTB +dtb7.DiemTB+dtb8.DiemTB+dtb9.DiemTB) / 9,2) as DiemTrungBinhHocKy " + _
                    " from DiemTrungBinh dtb1,DiemTrungBinh dtb2,DiemTrungBinh dtb3,DiemTrungBinh dtb4,DiemTrungBinh dtb5,DiemTrungBinh dtb6,DiemTrungBinh dtb7,DiemTrungBinh dtb8,DiemTrungBinh dtb9, HocSinh hs, LopHoc lh, NamHoc nh" + _
                    " where hs.MaHS = dtb1.MaHS and lh.MaLH = hs.MaLH " + _
                    " and hs.MaHS = dtb2.MaHS " + _
                    " and hs.MaHS = dtb3.MaHS " + _
                    " and hs.MaHS = dtb4.MaHS " + _
                    " and hs.MaHS = dtb5.MaHS " + _
                    " and hs.MaHS = dtb6.MaHS " + _
                    " and hs.MaHS = dtb7.MaHS " + _
                    " and hs.MaHS = dtb8.MaHS " + _
                    " and hs.MaHS = dtb9.MaHS " + _
                    " and dtb1.MaMH =1 " + _
                    " and dtb2.MaMH =2 " + _
                    " and dtb3.MaMH =3 " + _
                    " and dtb4.MaMH =4 " + _
                    " and dtb5.MaMH =5 " + _
                    " and dtb6.MaMH =6 " + _
                    " and dtb7.MaMH =7 " + _
                    " and dtb8.MaMH =8 " + _
                    " and dtb9.MaMH =9  " + _
                    " and dtb1.HocKy = " + hocKy.ToString() + _
                    " and dtb2.HocKy = " + hocKy.ToString() + _
                    " and dtb3.HocKy = " + hocKy.ToString() + _
                    " and dtb4.HocKy = " + hocKy.ToString() + _
                    " and dtb5.HocKy = " + hocKy.ToString() + _
                    " and dtb6.HocKy = " + hocKy.ToString() + _
                    " and dtb7.HocKy = " + hocKy.ToString() + _
                    " and dtb8.HocKy = " + hocKy.ToString() + _
                    " and dtb9.HocKy = " + hocKy.ToString() + _
                    " and nh.MaNH = lh.MaNam " + _
                    " and nh.MaNH =" + maNH.ToString() + _
                    " ORDER BY hs.HoTen"



        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter(strSQL, cn)
        da.Fill(dt)
        'B5: Dong ket noi CSDL
        cn.Close()
        Return dt
    End Function



End Class
